﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.ServiceModel.Activation;
using VAdvantage.Utility;
using VAdvantage.WF;
using VAdvantage.Logging;
using VAdvantage.DataBase;
using VAdvantage.ProcessEngine;
using VAdvantage.Model;
using VAdvantage.Acct;
using VAdvantage.Classes;
using VAdvantage.Print;
using VAdvantage.Process;
using System.IO;
using System.Web.Hosting;
using System.Xml;
using System.Data;
using VAdvantage.ImpExp;
using System.Runtime.Serialization;
using System.Threading;
using CrystalDecisions.CrystalReports.Engine;
using System.Data.OracleClient;
using System.Runtime.CompilerServices;
using VAdvantageSvc.Classes;


namespace VAdvantageSvc
{
    [ServiceContract(Namespace = "VAdvantageSvc")]
    public interface IDSProcessWorkflow
    {
        [OperationContract]
        Dictionary<String, Object> GetProcessInfo(Dictionary<String, Object> inProcessInfo, IDictionary<String, String> ctxMap, int AD_Workflow_ID, out CustomException serviceError);
        [OperationContract]
        String PostImmediate(IDictionary<string, string> ctx, int AD_Client_ID, int AD_Table_ID, int Record_ID, bool force, String trxName);
        [OperationContract]
        ReportInfo ExecuteProcess(IDictionary<string, string> ctxDic, int AD_Process_ID, string _Name, int AD_PInstance_ID, int AD_Table_ID, int Record_ID);
        [OperationContract]
        ReportInfo GeneratePdfReport(IDictionary<string, string> ctxDic, int id, List<string> queryInfo, Object code, bool isCreateNew, Dictionary<string, object> nProcessInfo);
        [OperationContract]
        byte[] GenerateCSVReport(IDictionary<string, string> ctxDic, int id, List<string> queryInfo, Object code);

        [OperationContract]
        void DoWork();

        [OperationContract]
        string DeserializeFromXML(int userid);

        [OperationContract]
        void SerializeToXML(string countryName, string cityName, IDictionary<string, string> ctxDic);


        [OperationContract]
        byte[] GenerateCrystalReport(CrystalParams cParams, out CustomException serviceError);

        [OperationContract]
        String GenerateCrystalReportString(CrystalParams cParams, out CustomException serviceError);

    }
    
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerCall, ConcurrencyMode = ConcurrencyMode.Single)]
    public class DSProcessWorkflow : IDSProcessWorkflow
    {
        public DSProcessWorkflow()
        {
        }

        private string connectionString = System.Configuration.ConfigurationSettings.AppSettings["oracleConnectionString"];


        public Dictionary<string, object> GetProcessInfo(Dictionary<string, object> inProcessInfo, IDictionary<string, string> ctxMap, int AD_Workflow_ID, out CustomException serviceError)
        {
            Dictionary<string, object> lst = null;
            serviceError = null;

            //ViennaAdvantageServer.WorkflowActivity wf = new ViennaAdvantageServer.WorkflowActivity();

            //lst =  wf.RunWorkFlowActivity(inProcessInfo, ctxMap, AD_Workflow_ID);
            //if (lst == null)
            //{
            //    serviceError = new CustomException(new Exception("Error in WorkflowActivity"));
            //}
            return lst;
        }

        private static VLogger log = null;

        public static bool StartUp(bool isClient, bool isUserProperty, String info)
        {
            bool OK = true;
            if (log != null)
                return true;

            //VLogMgt.Initialize(isClient);
            // Ini.SetClient(isUserProperty);

            //log = VLogger.GetVLogger(typeof(DSProcessWorkflow).FullName);
            //Ini.LoadProperties(false);

            //VLogMgt.SetLevel(Level.OFF);//  Ini.GetProperty(Ini.P_TRACELEVEL));

            //if (isClient && Ini.IsPropertyBool(Ini.P_TRACEFILE)
            //    && VLogFile.Get(false, null, isClient) == null)
            //{
            //    VLogMgt.AddHandler(VLogFile.Get(true, Ini.FindHome(), isClient));
            //}

            //	Set UI
            //if (isClient)
            //{

            //    if (VLogMgt.IsLevelAll())
            //        log.Log(Level.FINEST, "");
            //}

            //String attributes = Ini.GetProperty(Ini.P_CONNECTION);
            //if (string.IsNullOrEmpty(attributes))
            //{
            //    VAdvantage.Framework.CConnectionDialog conndgl = new VAdvantage.Framework.CConnectionDialog();
            //    conndgl.ShowDialog();
            //}

            if (isClient)		//	don't test connection
                return false;	//	need to call

            return true;
        }

        public String PostImmediate(IDictionary<string, string> ctxDic, int AD_Client_ID, int AD_Table_ID, int Record_ID, bool force, String trxName)
        {
            //log.Info("[" + _no + "] Table=" + AD_Table_ID + ", Record=" + Record_ID);
            //_postCount++;
            Ctx ctx = new Ctx(ctxDic);

            // Context ctx1 = new Context(ctxDic);


            ////////log/////
            string clientName = ctx.GetAD_Org_Name() + "_" + ctx.GetAD_User_Name();
            string storedPath = Path.Combine(HostingEnvironment.ApplicationPhysicalPath, "");
            storedPath += clientName;
            VLogMgt.Initialize(true, storedPath);
            // VLogMgt.AddHandler(VLogFile.Get(true, storedPath, true));
            /////////////////

            MAcctSchema[] ass = MAcctSchema.GetClientAcctSchema(ctx, AD_Client_ID);
            return Doc.PostImmediate(ass, AD_Table_ID, Record_ID, force, trxName);
        }

        #region IDSProcessWorkflow Members

        [MethodImpl(MethodImplOptions.Synchronized)]
        public ReportInfo ExecuteProcess(IDictionary<string, string> ctxDic, int AD_Process_ID, string _Name, int AD_PInstance_ID, int AD_Table_ID, int Record_ID)
        {
            //Ctx ctx = new Ctx(ctxDic);
            Context ctx = new Context(ctxDic);
            ReportEngine_N re = null;
            Env.SetContext(ctx);
            string lang = ctx.GetAD_Language().Replace("_", "-");
            System.Globalization.CultureInfo original = System.Threading.Thread.CurrentThread.CurrentCulture;

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(lang);
            System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(lang);


            ////////log/////
            string clientName = ctx.GetAD_Org_Name() + "_" + ctx.GetAD_User_Name();
            string storedPath = Path.Combine(HostingEnvironment.ApplicationPhysicalPath, "");
            storedPath += clientName;
            VLogMgt.Initialize(true, storedPath);
            // VLogMgt.AddHandler(VLogFile.Get(true, storedPath, true));
            ////////////////

            byte[] report = null;
            ReportInfo rep = null;
            try
            {
                ProcessInfo pi = new ProcessInfo(_Name, AD_Process_ID, AD_Table_ID, Record_ID);
                pi.SetAD_User_ID(ctx.GetAD_User_ID());
                pi.SetAD_Client_ID(ctx.GetAD_Client_ID());
                pi.SetAD_PInstance_ID(AD_PInstance_ID);

                //report = null;
                ProcessCtl ctl = new ProcessCtl();
                Dictionary<string, object> d = ctl.Process(pi, ctx, out report, out re);
                rep = new ReportInfo();
                rep.ReportProcessInfo = d;
                rep.Report = report;

                //Env.GetCtx().Clear();
            }
            catch
            { }

            System.Threading.Thread.CurrentThread.CurrentCulture = original;
            System.Threading.Thread.CurrentThread.CurrentUICulture = original;

            Env.GetContext().Clear();

            return rep;
        }

        #endregion

        #region Server Report - PDF Format

        public byte[] GenerateCSVReport(IDictionary<string, string> ctxDic, int id, List<string> queryInfo, Object code)
        {
            string tableName = queryInfo[0];
            string wherClause = queryInfo[1];

            Ctx _ctx = new Ctx(ctxDic);
            Query _query = new Query(tableName);


            ////////log/////
            string clientName = _ctx.GetAD_Org_Name() + "_" + _ctx.GetAD_User_Name();
            string storedPath = Path.Combine(HostingEnvironment.ApplicationPhysicalPath, "");
            storedPath += clientName;
            VLogMgt.Initialize(true, storedPath);
            // VLogMgt.AddHandler(VLogFile.Get(true, storedPath, true));
            //////////////////////


            if (!string.IsNullOrEmpty(wherClause))
                _query.AddRestriction(wherClause);

            int Record_ID = 0;
            if (_query.GetRestrictionCount() == 1 && (code).GetType() == typeof(int))
                Record_ID = ((int)code);

            MPrintFormat pf = MPrintFormat.Get(_ctx, id, true);

            PrintInfo info = new PrintInfo(pf.GetName(), pf.GetAD_Table_ID(), Record_ID);
            info.SetDescription(_query.GetInfo());
            ReportEngine_N re = new ReportEngine_N(_ctx, pf, _query, info);
            re.GetView();
            byte[] b = re.CreateCSV(_ctx);
            return b;

        }

        public ReportInfo GeneratePdfReport(IDictionary<string, string> ctxDic, int id, List<string> queryInfo, Object code, bool isCreateNew, Dictionary<string, object> nProcessInfo)
        {
            ReportInfo rep = null;
            ReportEngine_N re = null;
            Query _query = null;
            int Record_ID = 0;

            if (queryInfo.Count > 0)
            {
                string tableName = queryInfo[0];
                string wherClause = queryInfo[1];
                _query = new Query(tableName);

                if (!string.IsNullOrEmpty(wherClause))
                    _query.AddRestriction(wherClause);


                if (_query.GetRestrictionCount() == 1 && (code).GetType() == typeof(int))
                    Record_ID = ((int)code);
            }

            Context _ctx  = new Context(ctxDic);
            Env.SetContext(_ctx);
            string lang = _ctx.GetAD_Language().Replace("_", "-");


            System.Globalization.CultureInfo original = System.Threading.Thread.CurrentThread.CurrentCulture;

            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(lang);
            System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(lang);



            byte[] b = null;
            try
            {

                ////////log/////
                string clientName = _ctx.GetAD_Org_Name() + "_" + _ctx.GetAD_User_Name();
                string storedPath = Path.Combine(HostingEnvironment.ApplicationPhysicalPath, "");
                storedPath += clientName;
                VLogMgt.Initialize(true, storedPath);
                //VLogMgt.AddHandler(VLogFile.Get(true, storedPath, true));
                ////////////////

                MPrintFormat pf = null;

                if (!isCreateNew)
                    pf = MPrintFormat.Get(_ctx, id, true);
                else
                    pf = MPrintFormat.CreateFromTable(_ctx, id);

                rep = new ReportInfo();

                if (nProcessInfo == null)
                {
                    PrintInfo info = new PrintInfo(pf.GetName(), pf.GetAD_Table_ID(), Record_ID);
                    info.SetDescription(_query.GetInfo());
                    re = new ReportEngine_N(_ctx, pf, _query, info);
                }
                else
                {
                    ProcessInfo pi = new ProcessInfo().FromList(nProcessInfo);
                    pi.Set_AD_PrintFormat_ID(id);
                    //re = ReportCtl.Start(_ctx, pi, false);

                    ProcessCtl ctl = new ProcessCtl();
                    ctl.Process(pi, _ctx, out b, out re);
                    re.SetPrintFormat(pf);
                }

                re.GetView();
                b = re.CreatePDF();
                rep.Report = b;
                rep.AD_PrintFormat_ID = re.GetPrintFormat().GetAD_PrintFormat_ID();
                rep.ReportProcessInfo = null;
            }
            catch
            {
            }

            System.Threading.Thread.CurrentThread.CurrentCulture = original;
            System.Threading.Thread.CurrentThread.CurrentUICulture = original;

            Env.GetContext().Clear();
            return rep;
        }
        #endregion


        public void DoWork()
        {
            // Add your operation implementation here
            return;
        }

        public string DeserializeFromXML(int userid)
        {
            string location = "";
            IDataReader dr = null;
            try
            {
                string SQL = "SELECT City, Country FROM AD_WeatherInfo WHERE AD_User_ID = " + userid;
                dr = DB.ExecuteReader(SQL);
                while (dr.Read())
                {
                    location = dr[1].ToString() + "#" + dr[0].ToString();
                }
                dr.Close();
                dr = null;
            }
            catch 
            {
                if (dr != null)
                    dr.Close();
                dr = null;
                location = "";
            }
            return location;
        }

        public void SerializeToXML(string countryName, string cityName, IDictionary<string, string> ctxDic)
        {
            try
            {
                Ctx ctx = new Ctx(ctxDic);

                string SQL = "DELETE from AD_WeatherInfo WHERE AD_User_ID = " + ctx.GetAD_User_ID();
                int result = DB.ExecuteQuery(SQL);

                X_AD_WeatherInfo weather = new X_AD_WeatherInfo(ctx, 0, null);
                weather.SetAD_User_ID(ctx.GetAD_User_ID());
                weather.SetCity(cityName);
                weather.SetCountry(countryName);

                bool b = weather.Save();
            }
            catch
            {
            }
        }


        #region IDSProcessWorkflow Members



        public byte[] GenerateCrystalReport(CrystalParams cParams, out CustomException serviceError)
        {

             string reportPath =  System.IO.Path.Combine(System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath, "CReports\\Reports");
             string reportImagePath = System.IO.Path.Combine(System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath, "CReports\\Images");



             System.Globalization.CultureInfo systemCulture = Thread.CurrentThread.CurrentCulture;

             if (!string.IsNullOrEmpty(cParams.Culture))
             {
                 Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(cParams.Culture);
                 Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(cParams.Culture);
             }


            if (!String.IsNullOrEmpty(cParams.ProcedureName))
            {
                bool b = StartDBProcess(cParams.ProcedureName, cParams.ColNames, cParams.ColValues);
                if (!b)
                {
                    serviceError = new CustomException(new Exception("CouldNotExecuteProcedure"));
                    Thread.CurrentThread.CurrentCulture = systemCulture;
                    Thread.CurrentThread.CurrentUICulture = systemCulture;

                    return null;
                }

            }


            int AD_Form_ID = cParams.AD_Form_ID;
            int AD_CrystalInstance_ID = cParams.AD_CrystalInstance_ID;
            int AD_Client_ID = cParams.AD_Client_ID;


            serviceError = null;
            ProcessInfoParameter[] parameters = ProcessInfoUtil.SetCrystalParameterFromDB(AD_CrystalInstance_ID);
            MForm form = new VAdvantage.Model.MForm(Env.GetCtx(), AD_Form_ID, null);
            string _ReportImagePath = "";
            string _ReportPath = "";

            if (form.IsReport())
            {
                //MClientInfo clientinfo = new MClientInfo(Env.GetCtx(), AD_Client_ID, null);
                //string path = clientinfo.GetCrystalReportPath();
                string path = reportPath;
                //_ReportImagePath = clientinfo.GetCrystalImagePath();
                _ReportImagePath = reportImagePath;
                //log.Info(path);
                //log.Info(_ReportImagePath);
                if (String.IsNullOrEmpty(path))
                {
                    // ShowMessage.Error("CrystalReportPathNotSetForTenant", false);
                    // return;
                }
                if (form.GetReportPath().IndexOf(":") < 0)
                    _ReportPath = path + "\\" + form.GetReportPath();
                else
                    _ReportPath = form.GetReportPath();
            }
            else
            {
                serviceError = new CustomException(new Exception("Form Mapped can be used only for Reports"));
                // ShowMessage.Error("Form Mapped can be used only for Reports.", false);
                return null;
            }

            ReportDocument rptBurndown = new ReportDocument();
            if (File.Exists(_ReportPath))   //Check if the crystal report file exists in a specified location.
            {
                try
                {
                    rptBurndown.Load(_ReportPath);

                    //Set Connection Info
                    ConnectionInfo.Get().SetAttributes(connectionString);


                    //Application will pick database info from the property file.
                    CrystalDecisions.Shared.ConnectionInfo crDbConnection = new CrystalDecisions.Shared.ConnectionInfo();
                    crDbConnection.IntegratedSecurity = false;
                    crDbConnection.DatabaseName = ConnectionInfo.Get().Db_name;
                    crDbConnection.UserID = ConnectionInfo.Get().Db_uid;
                    crDbConnection.Password = ConnectionInfo.Get().Db_pwd;
                    //crDbConnection.Type = ConnectionInfoType.Unknown;
                    crDbConnection.ServerName = ConnectionInfo.Get().Db_host;
                    CrystalDecisions.CrystalReports.Engine.Database crDatabase = rptBurndown.Database;
                    CrystalDecisions.Shared.TableLogOnInfo oCrTableLoginInfo;
                    foreach (CrystalDecisions.CrystalReports.Engine.Table oCrTable in crDatabase.Tables)
                    {
                        crDbConnection.IntegratedSecurity = false;
                        crDbConnection.DatabaseName = ConnectionInfo.Get().Db_name;
                        crDbConnection.UserID = ConnectionInfo.Get().Db_uid;
                        crDbConnection.Password = ConnectionInfo.Get().Db_pwd;
                        //crDbConnection.Type = ConnectionInfoType.Unknown;
                        crDbConnection.ServerName = ConnectionInfo.Get().Db_host;

                        oCrTableLoginInfo = oCrTable.LogOnInfo;
                        oCrTableLoginInfo.ConnectionInfo = crDbConnection;
                        oCrTable.ApplyLogOnInfo(oCrTableLoginInfo);
                    }

                    //Create Parameter query
                    string sql = form.GetSqlQuery();
                    StringBuilder sb = new StringBuilder(" Where ");
                    if (parameters.Count() > 0)
                    {
                        int loopCount = 0;
                        for (int para = 0; para <= parameters.Count() - 1; para++)
                        {
                            string sInfo = parameters[para].GetInfo();
                            string sInfoTo = parameters[para].GetInfo_To();
                            if ((String.IsNullOrEmpty(sInfo) && String.IsNullOrEmpty(sInfoTo)) || sInfo == "NULL")
                            {
                                continue;
                            }

                            if (loopCount > 0)
                                sb.Append(" And ");
                            string paramName = parameters[para].GetParameterName();
                            object paramValue = parameters[para].GetParameter();
                            object paramValueTo = parameters[para].GetParameter_To();

                            if (paramValue is DateTime)
                            {
                                sb.Append(paramName).Append(" Between ").Append(GlobalVariable.TO_DATE((DateTime)paramValue, true));
                                if (paramValueTo != null && paramValueTo != String.Empty)
                                    sb.Append(" And ").Append(GlobalVariable.TO_DATE(((DateTime)paramValueTo).AddDays(1), true));
                                else
                                    sb.Append(" And ").Append(GlobalVariable.TO_DATE(((DateTime)paramValue).AddDays(1), true));

                            }
                            else
                            {
                                sb.Append("Upper(").Append(paramName).Append(")").Append(" = Upper(")
                                    .Append(GlobalVariable.TO_STRING(paramValue.ToString()) + ")");
                            }

                            loopCount++;
                        }

                        if (sb.Length > 7)
                            sql = sql + sb.ToString();
                    }

                    if (form.IsIncludeProcedure())
                    {
                        bool result = StartDBProcess(form.GetProcedureName(), parameters);
                    }

                    DataSet ds = DB.ExecuteDataset(sql);

                    if (ds == null)
                    {
                        ValueNamePair error = VLogger.RetrieveError();
                        serviceError = new CustomException(new Exception(error.GetValue() + "BlankReportWillOpen"));
                        //ShowMessage.Error(error.GetValue(), false, "BlankReportWillOpen");
                        //_frame.Close();
                        return null;
                    }

                    bool imageError = false;
                    if (form.IsIncludeImage())
                    {
                        for (int i_img = 0; i_img <= ds.Tables[0].Rows.Count - 1; i_img++)
                        {
                            String ImagePath = "";
                            String ImageField = "";
                            if (ds.Tables[0].Rows[i_img][form.GetImagePathField()] != null)
                            {
                                ImagePath = ds.Tables[0].Rows[i_img][form.GetImagePathField()].ToString();
                                ImageField = form.GetImageField();

                                if (ds.Tables[0].Columns.Contains(ImageField))
                                {
                                    if (File.Exists(_ReportImagePath + "\\" + ImagePath))
                                    {
                                        byte[] b = StreamFile(_ReportImagePath + "\\" + ImagePath);
                                        ds.Tables[0].Rows[i_img][ImageField] = b;
                                    }
                                    else
                                    {
                                        //ds.Tables[0].Rows.RemoveAt(i_img);
                                        imageError = true;
                                    }
                                }
                                else
                                {
                                    imageError = true;
                                }
                            }
                            else
                            {
                                imageError = true;
                            }
                        }
                    }

                    if (imageError)
                    {
                        //   ShowMessage.Error("ErrorLoadingSomeImages", true);
                    }


                    
                    //crystalReportViewer1.ReportSource = rptBurndown;
                    //crystalReportViewer1.Refresh();

                    System.IO.Stream oStream;
                    byte[] byteArray = null;
                   
                    rptBurndown.SetDataSource(ds.Tables[0]);                //By karan approveed by lokesh......
                    //rptBurndown.PrintOptions.ApplyPageMargins(new CrystalDecisions.Shared.PageMargins(100, 360, 100, 360));
                    oStream = rptBurndown.ExportToStream(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat);
                    byteArray = new byte[oStream.Length];
                    oStream.Read(byteArray, 0, Convert.ToInt32(oStream.Length));

                    Thread.CurrentThread.CurrentCulture = systemCulture;
                    Thread.CurrentThread.CurrentUICulture = systemCulture;

                    return byteArray;

                    if (form.IsDirectPrint())
                    {
                        // rptBurndown.PrintOptions.PrinterName = Env.GetCtx().GetPrinterName();
                        //rptBurndown.PrintToPrinter(1, false, 0, 0);
                    }
                }
                catch (Exception ex)
                {
                    serviceError = new CustomException(new Exception(ex.Message));
                    Thread.CurrentThread.CurrentCulture = systemCulture;
                    Thread.CurrentThread.CurrentUICulture = systemCulture;
                    return null;
                }
            }
            else
            {
                serviceError = new CustomException(new Exception("CouldNotFindTheCrystalReport"));
                //ShowMessage.Error("CouldNotFindTheCrystalReport", false);
            }
            Thread.CurrentThread.CurrentCulture = systemCulture;
            Thread.CurrentThread.CurrentUICulture = systemCulture;
            return null;
        }

        public String GenerateCrystalReportString(CrystalParams cParams, out CustomException serviceError)
        {

            string reportPath = System.IO.Path.Combine(System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath, "CReports\\Reports");
            string reportImagePath = System.IO.Path.Combine(System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath, "CReports\\Images");


            System.Globalization.CultureInfo systemCulture = Thread.CurrentThread.CurrentCulture;

            if (!string.IsNullOrEmpty(cParams.Culture))
            {
                Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(cParams.Culture);
                Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo(cParams.Culture);
            }


            if (!String.IsNullOrEmpty(cParams.ProcedureName))
            {
                bool b = StartDBProcess(cParams.ProcedureName, cParams.ColNames, cParams.ColValues);
                if (!b)
                {
                    serviceError = new CustomException(new Exception("CouldNotExecuteProcedure"));
                    //this.Cursor = Cursors.Default;

                    Thread.CurrentThread.CurrentCulture = systemCulture;
                    Thread.CurrentThread.CurrentUICulture = systemCulture;

                    return null;
                }

            }

            int AD_Form_ID = cParams.AD_Form_ID;
            int AD_CrystalInstance_ID = cParams.AD_CrystalInstance_ID;
            int AD_Client_ID = cParams.AD_Client_ID;


            serviceError = null;
            ProcessInfoParameter[] parameters = ProcessInfoUtil.SetCrystalParameterFromDB(AD_CrystalInstance_ID);
            MForm form = new VAdvantage.Model.MForm(Env.GetCtx(), AD_Form_ID, null);
            string _ReportImagePath = "";
            string _ReportPath = "";

            if (form.IsReport())
            {
                MClientInfo clientinfo = new MClientInfo(Env.GetCtx(), AD_Client_ID, null);
                //string path = clientinfo.GetCrystalReportPath();
                string path = reportPath;
                //_ReportImagePath = clientinfo.GetCrystalImagePath();
                _ReportImagePath = reportImagePath;
                //log.Info(path);
                //log.Info(_ReportImagePath);
                if (String.IsNullOrEmpty(path))
                {
                    // ShowMessage.Error("CrystalReportPathNotSetForTenant", false);
                    // return;
                }
                if (form.GetReportPath().IndexOf(":") < 0)
                    _ReportPath = path + "\\" + form.GetReportPath();
                else
                    _ReportPath = form.GetReportPath();
            }
            else
            {
                serviceError = new CustomException(new Exception("Form Mapped can be used only for Reports"));
                // ShowMessage.Error("Form Mapped can be used only for Reports.", false);
                Thread.CurrentThread.CurrentCulture = systemCulture;
                Thread.CurrentThread.CurrentUICulture = systemCulture;
                return null;
            }

            ReportDocument rptBurndown = new ReportDocument();
            if (File.Exists(_ReportPath))   //Check if the crystal report file exists in a specified location.
            {
                rptBurndown.Load(_ReportPath);

                //Set Connection Info
                ConnectionInfo.Get().SetAttributes(connectionString);


                //Application will pick database info from the property file.
                CrystalDecisions.Shared.ConnectionInfo crDbConnection = new CrystalDecisions.Shared.ConnectionInfo();
                crDbConnection.IntegratedSecurity = false;
                crDbConnection.DatabaseName = ConnectionInfo.Get().Db_name;
                crDbConnection.UserID = ConnectionInfo.Get().Db_uid;
                crDbConnection.Password = ConnectionInfo.Get().Db_pwd;
                //crDbConnection.Type = ConnectionInfoType.Unknown;
                crDbConnection.ServerName = ConnectionInfo.Get().Db_host;
                CrystalDecisions.CrystalReports.Engine.Database crDatabase = rptBurndown.Database;
                CrystalDecisions.Shared.TableLogOnInfo oCrTableLoginInfo;
                foreach (CrystalDecisions.CrystalReports.Engine.Table oCrTable in crDatabase.Tables)
                {
                    crDbConnection.IntegratedSecurity = false;
                    crDbConnection.DatabaseName = ConnectionInfo.Get().Db_name;
                    crDbConnection.UserID = ConnectionInfo.Get().Db_uid;
                    crDbConnection.Password = ConnectionInfo.Get().Db_pwd;
                    //crDbConnection.Type = ConnectionInfoType.Unknown;
                    crDbConnection.ServerName = ConnectionInfo.Get().Db_host;

                    oCrTableLoginInfo = oCrTable.LogOnInfo;
                    oCrTableLoginInfo.ConnectionInfo = crDbConnection;
                    oCrTable.ApplyLogOnInfo(oCrTableLoginInfo);
                }

                //Create Parameter query
                string sql = form.GetSqlQuery();
                StringBuilder sb = new StringBuilder(" Where ");
                if (parameters.Count() > 0)
                {
                    int loopCount = 0;
                    for (int para = 0; para <= parameters.Count() - 1; para++)
                    {
                        string sInfo = parameters[para].GetInfo();
                        string sInfoTo = parameters[para].GetInfo_To();
                        if ((String.IsNullOrEmpty(sInfo) && String.IsNullOrEmpty(sInfoTo)) || sInfo == "NULL")
                        {
                            continue;
                        }

                        if (loopCount > 0)
                            sb.Append(" And ");
                        string paramName = parameters[para].GetParameterName();
                        object paramValue = parameters[para].GetParameter();
                        object paramValueTo = parameters[para].GetParameter_To();

                        if (paramValue is DateTime)
                        {
                            sb.Append(paramName).Append(" Between ").Append(GlobalVariable.TO_DATE((DateTime)paramValue, true));
                            if (paramValueTo != null && paramValueTo != String.Empty)
                                sb.Append(" And ").Append(GlobalVariable.TO_DATE(((DateTime)paramValueTo).AddDays(1), true));
                            else
                                sb.Append(" And ").Append(GlobalVariable.TO_DATE(((DateTime)paramValue).AddDays(1), true));

                        }
                        else
                        {
                            sb.Append("Upper(").Append(paramName).Append(")").Append(" = Upper(")
                                .Append(GlobalVariable.TO_STRING(paramValue.ToString()) + ")");
                        }

                        loopCount++;
                    }

                    if (sb.Length > 7)
                        sql = sql + sb.ToString();
                }

                if (form.IsIncludeProcedure())
                {
                    bool result = StartDBProcess(form.GetProcedureName(), parameters);
                }

                DataSet ds = DB.ExecuteDataset(sql);

                if (ds == null)
                {
                    ValueNamePair error = VLogger.RetrieveError();
                    serviceError = new CustomException(new Exception(error.GetValue() + "BlankReportWillOpen"));
                    //ShowMessage.Error(error.GetValue(), false, "BlankReportWillOpen");
                    //_frame.Close();
                    Thread.CurrentThread.CurrentCulture = systemCulture;
                    Thread.CurrentThread.CurrentUICulture = systemCulture;
                    return null;
                }

                bool imageError = false;
                if (form.IsIncludeImage())
                {
                    for (int i_img = 0; i_img <= ds.Tables[0].Rows.Count - 1; i_img++)
                    {
                        String ImagePath = "";
                        String ImageField = "";
                        if (ds.Tables[0].Rows[i_img][form.GetImagePathField()] != null)
                        {
                            ImagePath = ds.Tables[0].Rows[i_img][form.GetImagePathField()].ToString();
                            ImageField = form.GetImageField();

                            if (ds.Tables[0].Columns.Contains(ImageField))
                            {
                                if (File.Exists(_ReportImagePath + "\\" + ImagePath))
                                {
                                    byte[] b = StreamFile(_ReportImagePath + "\\" + ImagePath);
                                    ds.Tables[0].Rows[i_img][ImageField] = b;
                                }
                                else
                                {
                                    //ds.Tables[0].Rows.RemoveAt(i_img);
                                    imageError = true;
                                }
                            }
                            else
                            {
                                imageError = true;
                            }
                        }
                        else
                        {
                            imageError = true;
                        }
                    }
                }

                if (imageError)
                {
                    //   ShowMessage.Error("ErrorLoadingSomeImages", true);
                }


                rptBurndown.SetDataSource(ds.Tables[0]);
                //crystalReportViewer1.ReportSource = rptBurndown;
                //crystalReportViewer1.Refresh();

                System.IO.Stream oStream;
                byte[] byteArray = null;
                oStream = rptBurndown.ExportToStream(CrystalDecisions.Shared.ExportFormatType.HTML32);
                byteArray = new byte[oStream.Length];
                oStream.Read(byteArray, 0, Convert.ToInt32(oStream.Length - 1));

                StreamReader sr = new StreamReader(oStream);
                string str =   sr.ReadToEnd();
                Thread.CurrentThread.CurrentCulture = systemCulture;
                Thread.CurrentThread.CurrentUICulture = systemCulture;
                return str;

                if (form.IsDirectPrint())
                {
                    // rptBurndown.PrintOptions.PrinterName = Env.GetCtx().GetPrinterName();
                    //rptBurndown.PrintToPrinter(1, false, 0, 0);
                }
            }
            else
            {
                serviceError = new CustomException(new Exception("CouldNotFindTheCrystalReport"));
                //ShowMessage.Error("CouldNotFindTheCrystalReport", false);
            }
            Thread.CurrentThread.CurrentCulture = systemCulture;
            Thread.CurrentThread.CurrentUICulture = systemCulture;
            return null;
        }


        /// <summary>
        /// Start the DB Process
        /// </summary>
        /// <param name="procedureName">name of the process</param>
        /// <returns></returns>
        private bool StartDBProcess(String procedureName, ProcessInfoParameter[] list)
        {
            if (DatabaseType.IsPostgre)  //jz Only DB2 not support stored procedure now
            {
                return false;
            }

            //  execute on this thread/connection
            //String sql = "{call " + procedureName + "(" + _pi.GetAD_PInstance_ID() + ")}";
            try
            {
                //only oracle procedure are supported
                OracleCommand comm = new OracleCommand();
                OracleConnection conn = (OracleConnection)VAdvantage.DataBase.DB.GetConnection();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = procedureName;
                comm.CommandType = CommandType.StoredProcedure;
                OracleCommandBuilder.DeriveParameters(comm);
                OracleParameter[] param = new OracleParameter[comm.Parameters.Count];
                int i = 0;
                StringBuilder orclParams = new StringBuilder();
                bool isDateTo = false;
                foreach (OracleParameter orp in comm.Parameters)
                {
                    if (isDateTo)
                    {
                        isDateTo = false;
                        continue;
                    }
                    Object paramvalue = list[i].GetParameter();
                    if (paramvalue != null)
                    {
                        if (orp.DbType == System.Data.DbType.DateTime)
                        {
                            if (paramvalue.ToString().Length > 0)
                            {
                                paramvalue = ((DateTime)paramvalue).ToString("dd-MMM-yyyy");
                            }
                            param[i] = new OracleParameter(orp.ParameterName, paramvalue);
                            if (list[i].GetParameter_To().ToString().Length > 0)
                            {
                                paramvalue = list[i].GetParameter_To();
                                paramvalue = ((DateTime)paramvalue).ToString("dd-MMM-yyyy");
                                param[i + 1] = new OracleParameter(comm.Parameters[i + 1].ParameterName, paramvalue);
                                i++;
                                isDateTo = true;
                                continue;
                            }
                            else
                            {
                                if (comm.Parameters.Count > (i + 1))
                                {
                                    if (comm.Parameters[i + 1].ParameterName.Equals(comm.Parameters[i].ParameterName + "_TO", StringComparison.OrdinalIgnoreCase))
                                    {
                                        param[i + 1] = new OracleParameter(comm.Parameters[i + 1].ParameterName, paramvalue);
                                        isDateTo = true;
                                        continue;
                                    }
                                }
                            }
                        }
                        else if (orp.DbType == System.Data.DbType.VarNumeric)
                        {
                            if (paramvalue.ToString().Length > 0)
                            {
                                //continue;
                            }
                            else
                                paramvalue = 0;
                        }
                        else
                        {
                            if (paramvalue.ToString().Length > 0)
                            {
                                paramvalue = GlobalVariable.TO_STRING(paramvalue.ToString());
                            }
                        }

                    }
                    param[i] = new OracleParameter(orp.ParameterName, paramvalue);
                    //orclParams.Append(orp.ParameterName).Append(": ").Append(_curTab.GetValue(list[i]));
                    //if (i < comm.Parameters.Count - 1)
                    //    orclParams.Append(", ");
                    i++;
                }

                //log.Fine("Executing " + procedureName + "(" + _pi.GetAD_PInstance_ID() + ")");
                int res = VAdvantage.SqlExec.Oracle.OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, procedureName, param);
                //DataBase.DB.ExecuteQuery(sql, null);
            }
            catch (Exception e)
            {
                VLogger.Get().SaveError(e.Message, e);
                //log.Log(Level.SEVERE, "Error executing procedure " + procedureName, e);
                return false;

            }
            //	log.fine(Log.l4_Data, "ProcessCtl.startProcess - done");
            return true;
        }

        private byte[] StreamFile(string filename)
        {
            FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read);

            // Create a byte array of file stream length
            byte[] ImageData = new byte[fs.Length];

            //Read block of bytes from stream into the byte array
            fs.Read(ImageData, 0, System.Convert.ToInt32(fs.Length));

            //Close the File Stream
            fs.Close();
            return ImageData; //return the byte data
        }

        private bool StartDBProcess(String procedureName, List<String> list,List<object> values)
        {
            if (DatabaseType.IsPostgre)  //jz Only DB2 not support stored procedure now
            {
                return false;
            }

            //  execute on this thread/connection
            //String sql = "{call " + procedureName + "(" + _pi.GetAD_PInstance_ID() + ")}";
            try
            {
                //only oracle procedure are supported
                OracleCommand comm = new OracleCommand();
                OracleConnection conn = (OracleConnection)VAdvantage.DataBase.DB.GetConnection();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = procedureName;
                comm.CommandType = CommandType.StoredProcedure;
                OracleCommandBuilder.DeriveParameters(comm);
                OracleParameter[] param = new OracleParameter[list.Count];
                int i = 0;
                StringBuilder orclParams = new StringBuilder();
                foreach (OracleParameter orp in comm.Parameters)
                {
                    param[i] = new OracleParameter(orp.ParameterName, values[i]);
                    orclParams.Append(orp.ParameterName).Append(": ").Append(values[i]);
                    if (i < comm.Parameters.Count - 1)
                        orclParams.Append(", ");
                    i++;
                }

                //log.Fine("Executing " + procedureName + "(" + _pi.GetAD_PInstance_ID() + ")");
                int res = VAdvantage.SqlExec.Oracle.OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, procedureName, param);
                //DataBase.DB.ExecuteQuery(sql, null);
            }
            catch (Exception e)
            {
                VLogger.Get().SaveError(e.Message, e);
                //log.Log(Level.SEVERE, "Error executing procedure " + procedureName, e);
                return false;

            }
            //	log.fine(Log.l4_Data, "ProcessCtl.startProcess - done");
            return true;
        }


       
        #endregion
    }
     
}
